{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:31.970038Z",
     "iopub.status.busy": "2021-05-29T14:57:31.969039Z",
     "iopub.status.idle": "2021-05-29T14:57:32.763118Z",
     "shell.execute_reply": "2021-05-29T14:57:32.761123Z",
     "shell.execute_reply.started": "2021-05-29T14:57:31.969039Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas.tseries.offsets import Hour,Minute,Day\n",
    "from dateutil.parser import parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:32.766105Z",
     "iopub.status.busy": "2021-05-29T14:57:32.765105Z",
     "iopub.status.idle": "2021-05-29T14:57:32.779103Z",
     "shell.execute_reply": "2021-05-29T14:57:32.777106Z",
     "shell.execute_reply.started": "2021-05-29T14:57:32.766105Z"
    }
   },
   "outputs": [],
   "source": [
    "cwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:32.782103Z",
     "iopub.status.busy": "2021-05-29T14:57:32.781104Z",
     "iopub.status.idle": "2021-05-29T14:57:32.795105Z",
     "shell.execute_reply": "2021-05-29T14:57:32.793105Z",
     "shell.execute_reply.started": "2021-05-29T14:57:32.781104Z"
    }
   },
   "outputs": [],
   "source": [
    "data_dir = os.path.join(cwd,\"数据\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:32.798105Z",
     "iopub.status.busy": "2021-05-29T14:57:32.797105Z",
     "iopub.status.idle": "2021-05-29T14:57:32.810105Z",
     "shell.execute_reply": "2021-05-29T14:57:32.809105Z",
     "shell.execute_reply.started": "2021-05-29T14:57:32.797105Z"
    }
   },
   "outputs": [],
   "source": [
    "data_files = [file for file in os.listdir(data_dir) if os.path.splitext(file)[1] in ['.csv']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:32.812106Z",
     "iopub.status.busy": "2021-05-29T14:57:32.812106Z",
     "iopub.status.idle": "2021-05-29T14:57:32.843109Z",
     "shell.execute_reply": "2021-05-29T14:57:32.841108Z",
     "shell.execute_reply.started": "2021-05-29T14:57:32.812106Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data_fam_fleet.csv',\n",
       " 'data_fam_market_share.csv',\n",
       " 'data_fam_products.csv',\n",
       " 'data_fam_schedule.csv',\n",
       " 'group1.csv',\n",
       " 'group2.csv',\n",
       " 'group3.csv',\n",
       " 'new_schedual.csv',\n",
       " 'new_schedual_v2.0.csv',\n",
       " '参考时刻表.csv']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 首先，提取所有出现的航线的时间(因为发现有的航线有转机) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:32.846110Z",
     "iopub.status.busy": "2021-05-29T14:57:32.845110Z",
     "iopub.status.idle": "2021-05-29T14:57:33.029123Z",
     "shell.execute_reply": "2021-05-29T14:57:33.028121Z",
     "shell.execute_reply.started": "2021-05-29T14:57:32.846110Z"
    }
   },
   "outputs": [],
   "source": [
    "raw_product = pd.read_csv(os.path.join(data_dir,data_files[2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.034123Z",
     "iopub.status.busy": "2021-05-29T14:57:33.033124Z",
     "iopub.status.idle": "2021-05-29T14:57:33.078128Z",
     "shell.execute_reply": "2021-05-29T14:57:33.076140Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.034123Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 07:55:00</td>\n",
       "      <td>2021-05-29 10:38:00</td>\n",
       "      <td>2021-05-29 11:55:00</td>\n",
       "      <td>2021-05-29 17:38:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 14:05:00</td>\n",
       "      <td>2021-05-29 21:59:00</td>\n",
       "      <td>2021-05-29 21:05:00</td>\n",
       "      <td>2021-05-30 01:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 17:40:00</td>\n",
       "      <td>2021-05-29 20:25:00</td>\n",
       "      <td>2021-05-29 21:40:00</td>\n",
       "      <td>2021-05-30 03:25:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 08:40:00</td>\n",
       "      <td>2021-05-29 16:40:00</td>\n",
       "      <td>2021-05-29 15:40:00</td>\n",
       "      <td>2021-05-29 20:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021-05-29 09:10:00</td>\n",
       "      <td>2021-05-29 12:02:00</td>\n",
       "      <td>2021-05-29 13:10:00</td>\n",
       "      <td>2021-05-29 19:02:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination              deptime              arrtime  \\\n",
       "0  AA0001    EDB         TFB  2021-05-29 07:55:00  2021-05-29 10:38:00   \n",
       "1  AA0002    TFB         EDB  2021-05-29 14:05:00  2021-05-29 21:59:00   \n",
       "2  AA0003    EDB         TFB  2021-05-29 17:40:00  2021-05-29 20:25:00   \n",
       "3  AA0004    TFB         EDB  2021-05-29 08:40:00  2021-05-29 16:40:00   \n",
       "4  AA0005    EDB         MBY  2021-05-29 09:10:00  2021-05-29 12:02:00   \n",
       "\n",
       "               dep_ucl              arr_ucl  \n",
       "0  2021-05-29 11:55:00  2021-05-29 17:38:00  \n",
       "1  2021-05-29 21:05:00  2021-05-30 01:59:00  \n",
       "2  2021-05-29 21:40:00  2021-05-30 03:25:00  \n",
       "3  2021-05-29 15:40:00  2021-05-29 20:40:00  \n",
       "4  2021-05-29 13:10:00  2021-05-29 19:02:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_schedual = pd.read_csv(os.path.join(data_dir,data_files[-2]),index_col=0)\n",
    "raw_schedual.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.081128Z",
     "iopub.status.busy": "2021-05-29T14:57:33.080126Z",
     "iopub.status.idle": "2021-05-29T14:57:33.109131Z",
     "shell.execute_reply": "2021-05-29T14:57:33.108127Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.081128Z"
    }
   },
   "outputs": [],
   "source": [
    "all_flight = pd.concat([raw_product.Flight1,raw_product.Flight2,\n",
    "                       raw_product.Flight3])\n",
    "all_flight = all_flight.replace('.',np.nan)\n",
    "all_flight = all_flight.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.112129Z",
     "iopub.status.busy": "2021-05-29T14:57:33.111130Z",
     "iopub.status.idle": "2021-05-29T14:57:33.174134Z",
     "shell.execute_reply": "2021-05-29T14:57:33.172134Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.112129Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count            86504\n",
       "unique             922\n",
       "top       AA0097TFBBOD\n",
       "freq               517\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_flight.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.176136Z",
     "iopub.status.busy": "2021-05-29T14:57:33.175135Z",
     "iopub.status.idle": "2021-05-29T14:57:33.206137Z",
     "shell.execute_reply": "2021-05-29T14:57:33.204136Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.176136Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table = pd.DataFrame(all_flight.unique(),columns=['flight'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.209139Z",
     "iopub.status.busy": "2021-05-29T14:57:33.208138Z",
     "iopub.status.idle": "2021-05-29T14:57:33.238141Z",
     "shell.execute_reply": "2021-05-29T14:57:33.236140Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.209139Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0040BERBOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0048BERBOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0047BODBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0049BODBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0112BODTFB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         flight\n",
       "0  AA0040BERBOD\n",
       "1  AA0048BERBOD\n",
       "2  AA0047BODBER\n",
       "3  AA0049BODBER\n",
       "4  AA0112BODTFB"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.241142Z",
     "iopub.status.busy": "2021-05-29T14:57:33.240141Z",
     "iopub.status.idle": "2021-05-29T14:57:33.269142Z",
     "shell.execute_reply": "2021-05-29T14:57:33.267144Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.241142Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>AA0516TFBTOB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              flight\n",
       "count            922\n",
       "unique           922\n",
       "top     AA0516TFBTOB\n",
       "freq               1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.271143Z",
     "iopub.status.busy": "2021-05-29T14:57:33.270143Z",
     "iopub.status.idle": "2021-05-29T14:57:33.396154Z",
     "shell.execute_reply": "2021-05-29T14:57:33.394171Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.271143Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table = pd.concat([new_schedual_table,pd.DataFrame(list(zip(*new_schedual_table.applymap(lambda x:(x[:6],x[6:9],x[9:12])).iloc[:,0]))).T],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.398167Z",
     "iopub.status.busy": "2021-05-29T14:57:33.397152Z",
     "iopub.status.idle": "2021-05-29T14:57:33.411153Z",
     "shell.execute_reply": "2021-05-29T14:57:33.410167Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.398167Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table.columns = ['Flight','flight','origin','destination']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.414157Z",
     "iopub.status.busy": "2021-05-29T14:57:33.413156Z",
     "iopub.status.idle": "2021-05-29T14:57:33.441158Z",
     "shell.execute_reply": "2021-05-29T14:57:33.440155Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.414157Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Flight</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0048</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0047BODBER</td>\n",
       "      <td>AA0047</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0049BODBER</td>\n",
       "      <td>AA0049</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0112</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Flight  flight origin destination\n",
       "0  AA0040BERBOD  AA0040    BER         BOD\n",
       "1  AA0048BERBOD  AA0048    BER         BOD\n",
       "2  AA0047BODBER  AA0047    BOD         BER\n",
       "3  AA0049BODBER  AA0049    BOD         BER\n",
       "4  AA0112BODTFB  AA0112    BOD         TFB"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.444158Z",
     "iopub.status.busy": "2021-05-29T14:57:33.443156Z",
     "iopub.status.idle": "2021-05-29T14:57:33.472160Z",
     "shell.execute_reply": "2021-05-29T14:57:33.471172Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.444158Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 07:55:00</td>\n",
       "      <td>2021-05-29 10:38:00</td>\n",
       "      <td>2021-05-29 11:55:00</td>\n",
       "      <td>2021-05-29 17:38:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 14:05:00</td>\n",
       "      <td>2021-05-29 21:59:00</td>\n",
       "      <td>2021-05-29 21:05:00</td>\n",
       "      <td>2021-05-30 01:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 17:40:00</td>\n",
       "      <td>2021-05-29 20:25:00</td>\n",
       "      <td>2021-05-29 21:40:00</td>\n",
       "      <td>2021-05-30 03:25:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 08:40:00</td>\n",
       "      <td>2021-05-29 16:40:00</td>\n",
       "      <td>2021-05-29 15:40:00</td>\n",
       "      <td>2021-05-29 20:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021-05-29 09:10:00</td>\n",
       "      <td>2021-05-29 12:02:00</td>\n",
       "      <td>2021-05-29 13:10:00</td>\n",
       "      <td>2021-05-29 19:02:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination              deptime              arrtime  \\\n",
       "0  AA0001    EDB         TFB  2021-05-29 07:55:00  2021-05-29 10:38:00   \n",
       "1  AA0002    TFB         EDB  2021-05-29 14:05:00  2021-05-29 21:59:00   \n",
       "2  AA0003    EDB         TFB  2021-05-29 17:40:00  2021-05-29 20:25:00   \n",
       "3  AA0004    TFB         EDB  2021-05-29 08:40:00  2021-05-29 16:40:00   \n",
       "4  AA0005    EDB         MBY  2021-05-29 09:10:00  2021-05-29 12:02:00   \n",
       "\n",
       "               dep_ucl              arr_ucl  \n",
       "0  2021-05-29 11:55:00  2021-05-29 17:38:00  \n",
       "1  2021-05-29 21:05:00  2021-05-30 01:59:00  \n",
       "2  2021-05-29 21:40:00  2021-05-30 03:25:00  \n",
       "3  2021-05-29 15:40:00  2021-05-29 20:40:00  \n",
       "4  2021-05-29 13:10:00  2021-05-29 19:02:00  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_schedual.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.475159Z",
     "iopub.status.busy": "2021-05-29T14:57:33.474160Z",
     "iopub.status.idle": "2021-05-29T14:57:33.506163Z",
     "shell.execute_reply": "2021-05-29T14:57:33.503161Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.474160Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table_1 = pd.merge(new_schedual_table,raw_schedual.loc[:,raw_schedual.columns != 'destination'],on=['flight','origin'])\n",
    "new_schedual_table_2 = pd.merge(new_schedual_table_1,raw_schedual.loc[:,raw_schedual.columns != 'origin'],on = ['flight','destination'],suffixes=['_ori','_des'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.509162Z",
     "iopub.status.busy": "2021-05-29T14:57:33.508165Z",
     "iopub.status.idle": "2021-05-29T14:57:33.536165Z",
     "shell.execute_reply": "2021-05-29T14:57:33.535165Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.509162Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Flight</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0048</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>2021-05-29 08:22:00</td>\n",
       "      <td>2021-05-29 09:13:00</td>\n",
       "      <td>2021-05-29 16:22:00</td>\n",
       "      <td>2021-05-29 17:13:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0047BODBER</td>\n",
       "      <td>AA0047</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "      <td>2021-05-29 06:30:00</td>\n",
       "      <td>2021-05-29 07:28:00</td>\n",
       "      <td>2021-05-29 14:30:00</td>\n",
       "      <td>2021-05-29 15:28:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0049BODBER</td>\n",
       "      <td>AA0049</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "      <td>2021-05-29 15:07:00</td>\n",
       "      <td>2021-05-29 16:04:00</td>\n",
       "      <td>2021-05-29 23:07:00</td>\n",
       "      <td>2021-05-30 00:04:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0112</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 01:30:00</td>\n",
       "      <td>2021-05-29 05:55:00</td>\n",
       "      <td>2021-05-29 09:30:00</td>\n",
       "      <td>2021-05-29 12:55:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Flight  flight origin destination              deptime  \\\n",
       "0  AA0040BERBOD  AA0040    BER         BOD  2021-05-29 16:53:00   \n",
       "1  AA0048BERBOD  AA0048    BER         BOD  2021-05-29 08:22:00   \n",
       "2  AA0047BODBER  AA0047    BOD         BER  2021-05-29 06:30:00   \n",
       "3  AA0049BODBER  AA0049    BOD         BER  2021-05-29 15:07:00   \n",
       "4  AA0112BODTFB  AA0112    BOD         TFB  2021-05-29 01:30:00   \n",
       "\n",
       "               arrtime              dep_ucl              arr_ucl  \n",
       "0  2021-05-29 17:47:00  2021-05-30 00:53:00  2021-05-30 01:47:00  \n",
       "1  2021-05-29 09:13:00  2021-05-29 16:22:00  2021-05-29 17:13:00  \n",
       "2  2021-05-29 07:28:00  2021-05-29 14:30:00  2021-05-29 15:28:00  \n",
       "3  2021-05-29 16:04:00  2021-05-29 23:07:00  2021-05-30 00:04:00  \n",
       "4  2021-05-29 05:55:00  2021-05-29 09:30:00  2021-05-29 12:55:00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.538165Z",
     "iopub.status.busy": "2021-05-29T14:57:33.537164Z",
     "iopub.status.idle": "2021-05-29T14:57:33.568172Z",
     "shell.execute_reply": "2021-05-29T14:57:33.566167Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.538165Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Flight</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime_ori</th>\n",
       "      <th>arrtime_ori</th>\n",
       "      <th>dep_ucl_ori</th>\n",
       "      <th>arr_ucl_ori</th>\n",
       "      <th>deptime_des</th>\n",
       "      <th>arrtime_des</th>\n",
       "      <th>dep_ucl_des</th>\n",
       "      <th>arr_ucl_des</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0048</td>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>2021-05-29 08:22:00</td>\n",
       "      <td>2021-05-29 09:13:00</td>\n",
       "      <td>2021-05-29 16:22:00</td>\n",
       "      <td>2021-05-29 17:13:00</td>\n",
       "      <td>2021-05-29 08:22:00</td>\n",
       "      <td>2021-05-29 09:13:00</td>\n",
       "      <td>2021-05-29 16:22:00</td>\n",
       "      <td>2021-05-29 17:13:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0047BODBER</td>\n",
       "      <td>AA0047</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "      <td>2021-05-29 06:30:00</td>\n",
       "      <td>2021-05-29 07:28:00</td>\n",
       "      <td>2021-05-29 14:30:00</td>\n",
       "      <td>2021-05-29 15:28:00</td>\n",
       "      <td>2021-05-29 06:30:00</td>\n",
       "      <td>2021-05-29 07:28:00</td>\n",
       "      <td>2021-05-29 14:30:00</td>\n",
       "      <td>2021-05-29 15:28:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0049BODBER</td>\n",
       "      <td>AA0049</td>\n",
       "      <td>BOD</td>\n",
       "      <td>BER</td>\n",
       "      <td>2021-05-29 15:07:00</td>\n",
       "      <td>2021-05-29 16:04:00</td>\n",
       "      <td>2021-05-29 23:07:00</td>\n",
       "      <td>2021-05-30 00:04:00</td>\n",
       "      <td>2021-05-29 15:07:00</td>\n",
       "      <td>2021-05-29 16:04:00</td>\n",
       "      <td>2021-05-29 23:07:00</td>\n",
       "      <td>2021-05-30 00:04:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0112</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 01:30:00</td>\n",
       "      <td>2021-05-29 05:55:00</td>\n",
       "      <td>2021-05-29 09:30:00</td>\n",
       "      <td>2021-05-29 12:55:00</td>\n",
       "      <td>2021-05-29 01:30:00</td>\n",
       "      <td>2021-05-29 05:55:00</td>\n",
       "      <td>2021-05-29 09:30:00</td>\n",
       "      <td>2021-05-29 12:55:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Flight  flight origin destination          deptime_ori  \\\n",
       "0  AA0040BERBOD  AA0040    BER         BOD  2021-05-29 16:53:00   \n",
       "1  AA0048BERBOD  AA0048    BER         BOD  2021-05-29 08:22:00   \n",
       "2  AA0047BODBER  AA0047    BOD         BER  2021-05-29 06:30:00   \n",
       "3  AA0049BODBER  AA0049    BOD         BER  2021-05-29 15:07:00   \n",
       "4  AA0112BODTFB  AA0112    BOD         TFB  2021-05-29 01:30:00   \n",
       "\n",
       "           arrtime_ori          dep_ucl_ori          arr_ucl_ori  \\\n",
       "0  2021-05-29 17:47:00  2021-05-30 00:53:00  2021-05-30 01:47:00   \n",
       "1  2021-05-29 09:13:00  2021-05-29 16:22:00  2021-05-29 17:13:00   \n",
       "2  2021-05-29 07:28:00  2021-05-29 14:30:00  2021-05-29 15:28:00   \n",
       "3  2021-05-29 16:04:00  2021-05-29 23:07:00  2021-05-30 00:04:00   \n",
       "4  2021-05-29 05:55:00  2021-05-29 09:30:00  2021-05-29 12:55:00   \n",
       "\n",
       "           deptime_des          arrtime_des          dep_ucl_des  \\\n",
       "0  2021-05-29 16:53:00  2021-05-29 17:47:00  2021-05-30 00:53:00   \n",
       "1  2021-05-29 08:22:00  2021-05-29 09:13:00  2021-05-29 16:22:00   \n",
       "2  2021-05-29 06:30:00  2021-05-29 07:28:00  2021-05-29 14:30:00   \n",
       "3  2021-05-29 15:07:00  2021-05-29 16:04:00  2021-05-29 23:07:00   \n",
       "4  2021-05-29 01:30:00  2021-05-29 05:55:00  2021-05-29 09:30:00   \n",
       "\n",
       "           arr_ucl_des  \n",
       "0  2021-05-30 01:47:00  \n",
       "1  2021-05-29 17:13:00  \n",
       "2  2021-05-29 15:28:00  \n",
       "3  2021-05-30 00:04:00  \n",
       "4  2021-05-29 12:55:00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.572167Z",
     "iopub.status.busy": "2021-05-29T14:57:33.571168Z",
     "iopub.status.idle": "2021-05-29T14:57:33.584168Z",
     "shell.execute_reply": "2021-05-29T14:57:33.582169Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.571168Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table = new_schedual_table_2.loc[:,:'destination']\n",
    "new_schedual_table['deptime'] = new_schedual_table_2.deptime_ori\n",
    "new_schedual_table['depucl'] = new_schedual_table_2.dep_ucl_ori\n",
    "new_schedual_table['arrtime'] = new_schedual_table_2.arrtime_des\n",
    "new_schedual_table['arrucl'] = new_schedual_table_2.arr_ucl_des"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.587168Z",
     "iopub.status.busy": "2021-05-29T14:57:33.586168Z",
     "iopub.status.idle": "2021-05-29T14:57:33.632173Z",
     "shell.execute_reply": "2021-05-29T14:57:33.630170Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.587168Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table = new_schedual_table.select_dtypes('object').apply(pd.to_datetime,errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.635173Z",
     "iopub.status.busy": "2021-05-29T14:57:33.634173Z",
     "iopub.status.idle": "2021-05-29T14:57:33.663175Z",
     "shell.execute_reply": "2021-05-29T14:57:33.662174Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.635173Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 922 entries, 0 to 921\n",
      "Data columns (total 8 columns):\n",
      " #   Column       Non-Null Count  Dtype         \n",
      "---  ------       --------------  -----         \n",
      " 0   Flight       922 non-null    object        \n",
      " 1   flight       922 non-null    object        \n",
      " 2   origin       922 non-null    object        \n",
      " 3   destination  922 non-null    object        \n",
      " 4   deptime      922 non-null    datetime64[ns]\n",
      " 5   depucl       922 non-null    datetime64[ns]\n",
      " 6   arrtime      922 non-null    datetime64[ns]\n",
      " 7   arrucl       922 non-null    datetime64[ns]\n",
      "dtypes: datetime64[ns](4), object(4)\n",
      "memory usage: 64.8+ KB\n"
     ]
    }
   ],
   "source": [
    "new_schedual_table.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.666174Z",
     "iopub.status.busy": "2021-05-29T14:57:33.665173Z",
     "iopub.status.idle": "2021-05-29T14:57:33.695177Z",
     "shell.execute_reply": "2021-05-29T14:57:33.693178Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.666174Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Flight</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>depucl</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>arrucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Flight, flight, origin, destination, deptime, depucl, arrtime, arrucl]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual_table[new_schedual_table.arrucl <= new_schedual_table.depucl]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.697176Z",
     "iopub.status.busy": "2021-05-29T14:57:33.696177Z",
     "iopub.status.idle": "2021-05-29T14:57:33.743180Z",
     "shell.execute_reply": "2021-05-29T14:57:33.741180Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.697176Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual_table.to_csv('参考时刻表.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 处理product中的数据 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.745180Z",
     "iopub.status.busy": "2021-05-29T14:57:33.744182Z",
     "iopub.status.idle": "2021-05-29T14:57:33.839189Z",
     "shell.execute_reply": "2021-05-29T14:57:33.837188Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.745180Z"
    }
   },
   "outputs": [],
   "source": [
    "import re\n",
    "groups = []\n",
    "for group_file in  filter(lambda x:re.match(r'group\\d.csv',x),data_files):\n",
    "    groups.append(pd.read_csv(os.path.join(data_dir,group_file),index_col=0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.841189Z",
     "iopub.status.busy": "2021-05-29T14:57:33.840188Z",
     "iopub.status.idle": "2021-05-29T14:57:33.871191Z",
     "shell.execute_reply": "2021-05-29T14:57:33.869192Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.841189Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>B</td>\n",
       "      <td>230.000000</td>\n",
       "      <td>2.290000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>G</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>2.070000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>H</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>0.660000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>K</td>\n",
       "      <td>94.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>L</td>\n",
       "      <td>145.000000</td>\n",
       "      <td>13.120000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47185</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702IOMCMJ</td>\n",
       "      <td>Q</td>\n",
       "      <td>211.548112</td>\n",
       "      <td>8.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47186</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702IOMCMJ</td>\n",
       "      <td>S</td>\n",
       "      <td>133.410115</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47187</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702IOMCMJ</td>\n",
       "      <td>T</td>\n",
       "      <td>312.683254</td>\n",
       "      <td>1.430000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47188</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702IOMCMJ</td>\n",
       "      <td>V</td>\n",
       "      <td>380.136667</td>\n",
       "      <td>14.603333</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47189</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702IOMCMJ</td>\n",
       "      <td>Y</td>\n",
       "      <td>490.195575</td>\n",
       "      <td>27.300000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10142 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Origin Destination       Flight1 Class        Fare        TRD  \\\n",
       "0        BER         BOD  AA0040BERBOD     B  230.000000   2.290000   \n",
       "1        BER         BOD  AA0040BERBOD     G  100.000000   2.070000   \n",
       "2        BER         BOD  AA0040BERBOD     H  105.000000   0.660000   \n",
       "3        BER         BOD  AA0040BERBOD     K   94.000000   0.000000   \n",
       "4        BER         BOD  AA0040BERBOD     L  145.000000  13.120000   \n",
       "...      ...         ...           ...   ...         ...        ...   \n",
       "47185    IOM         CMJ  AA2702IOMCMJ     Q  211.548112   8.666667   \n",
       "47186    IOM         CMJ  AA2702IOMCMJ     S  133.410115   0.000000   \n",
       "47187    IOM         CMJ  AA2702IOMCMJ     T  312.683254   1.430000   \n",
       "47188    IOM         CMJ  AA2702IOMCMJ     V  380.136667  14.603333   \n",
       "47189    IOM         CMJ  AA2702IOMCMJ     Y  490.195575  27.300000   \n",
       "\n",
       "       Transfer times  \n",
       "0                   1  \n",
       "1                   1  \n",
       "2                   1  \n",
       "3                   1  \n",
       "4                   1  \n",
       "...               ...  \n",
       "47185               1  \n",
       "47186               1  \n",
       "47187               1  \n",
       "47188               1  \n",
       "47189               1  \n",
       "\n",
       "[10142 rows x 7 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.873190Z",
     "iopub.status.busy": "2021-05-29T14:57:33.873190Z",
     "iopub.status.idle": "2021-05-29T14:57:33.887193Z",
     "shell.execute_reply": "2021-05-29T14:57:33.885202Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.873190Z"
    }
   },
   "outputs": [],
   "source": [
    "group1 = pd.merge(groups[0],new_schedual_table,left_on='Flight1',right_on='Flight',sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T14:57:33.893193Z",
     "iopub.status.busy": "2021-05-29T14:57:33.892194Z",
     "iopub.status.idle": "2021-05-29T14:57:36.475559Z",
     "shell.execute_reply": "2021-05-29T14:57:36.473574Z",
     "shell.execute_reply.started": "2021-05-29T14:57:33.893193Z"
    }
   },
   "outputs": [],
   "source": [
    "group1 = group1.T.drop_duplicates().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:18:40.087554Z",
     "iopub.status.busy": "2021-05-29T16:18:40.086537Z",
     "iopub.status.idle": "2021-05-29T16:18:40.120540Z",
     "shell.execute_reply": "2021-05-29T16:18:40.118552Z",
     "shell.execute_reply.started": "2021-05-29T16:18:40.087554Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "      <th>flight</th>\n",
       "      <th>deptime</th>\n",
       "      <th>depucl</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>arrucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>B</td>\n",
       "      <td>230</td>\n",
       "      <td>2.29</td>\n",
       "      <td>1</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>G</td>\n",
       "      <td>100</td>\n",
       "      <td>2.07</td>\n",
       "      <td>1</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>H</td>\n",
       "      <td>105</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>K</td>\n",
       "      <td>94</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>L</td>\n",
       "      <td>145</td>\n",
       "      <td>13.12</td>\n",
       "      <td>1</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>2021-05-29 16:53:00</td>\n",
       "      <td>2021-05-30 00:53:00</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Class Fare    TRD Transfer times  flight  \\\n",
       "0    BER         BOD  AA0040BERBOD     B  230   2.29              1  AA0040   \n",
       "1    BER         BOD  AA0040BERBOD     G  100   2.07              1  AA0040   \n",
       "2    BER         BOD  AA0040BERBOD     H  105   0.66              1  AA0040   \n",
       "3    BER         BOD  AA0040BERBOD     K   94      0              1  AA0040   \n",
       "4    BER         BOD  AA0040BERBOD     L  145  13.12              1  AA0040   \n",
       "\n",
       "              deptime              depucl             arrtime  \\\n",
       "0 2021-05-29 16:53:00 2021-05-30 00:53:00 2021-05-29 17:47:00   \n",
       "1 2021-05-29 16:53:00 2021-05-30 00:53:00 2021-05-29 17:47:00   \n",
       "2 2021-05-29 16:53:00 2021-05-30 00:53:00 2021-05-29 17:47:00   \n",
       "3 2021-05-29 16:53:00 2021-05-30 00:53:00 2021-05-29 17:47:00   \n",
       "4 2021-05-29 16:53:00 2021-05-30 00:53:00 2021-05-29 17:47:00   \n",
       "\n",
       "               arrucl  \n",
       "0 2021-05-30 01:47:00  \n",
       "1 2021-05-30 01:47:00  \n",
       "2 2021-05-30 01:47:00  \n",
       "3 2021-05-30 01:47:00  \n",
       "4 2021-05-30 01:47:00  "
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:19:31.336834Z",
     "iopub.status.busy": "2021-05-29T16:19:31.336834Z",
     "iopub.status.idle": "2021-05-29T16:19:31.477827Z",
     "shell.execute_reply": "2021-05-29T16:19:31.475826Z",
     "shell.execute_reply.started": "2021-05-29T16:19:31.336834Z"
    }
   },
   "outputs": [],
   "source": [
    "group1['delta_time'] = (group1.arrucl - group1.depucl).apply(lambda x:x.seconds / 60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 有一次转机的数据处理 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T15:47:01.111099Z",
     "iopub.status.busy": "2021-05-29T15:47:01.110099Z",
     "iopub.status.idle": "2021-05-29T15:47:01.121101Z",
     "shell.execute_reply": "2021-05-29T15:47:01.119242Z",
     "shell.execute_reply.started": "2021-05-29T15:47:01.111099Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_trans():\n",
    "    group = pd.merge(groups[1],new_schedual_table,left_on='Flight1',right_on='Flight',sort=False)\n",
    "    group = pd.merge(group,new_schedual_table,left_on='Flight2',right_on='Flight',sort=False,suffixes=['_1','_2'])\n",
    "    group.loc[group.arrucl_1 >= group.depucl_2,group.columns[-4:]] += Day(1)\n",
    "    return group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T15:47:50.327830Z",
     "iopub.status.busy": "2021-05-29T15:47:50.326832Z",
     "iopub.status.idle": "2021-05-29T15:47:50.385402Z",
     "shell.execute_reply": "2021-05-29T15:47:50.383402Z",
     "shell.execute_reply.started": "2021-05-29T15:47:50.327830Z"
    }
   },
   "outputs": [],
   "source": [
    "group2 = process_trans()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T15:47:53.176242Z",
     "iopub.status.busy": "2021-05-29T15:47:53.175241Z",
     "iopub.status.idle": "2021-05-29T15:47:53.206552Z",
     "shell.execute_reply": "2021-05-29T15:47:53.204548Z",
     "shell.execute_reply.started": "2021-05-29T15:47:53.176242Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>deptime_2</th>\n",
       "      <th>depucl_2</th>\n",
       "      <th>arrtime_2</th>\n",
       "      <th>arrucl_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [deptime_2, depucl_2, arrtime_2, arrucl_2]\n",
       "Index: []"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group2.loc[group2.arrucl_1 >= group2.depucl_2,group2.columns[-4:]] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T15:47:57.794286Z",
     "iopub.status.busy": "2021-05-29T15:47:57.794286Z",
     "iopub.status.idle": "2021-05-29T15:47:57.836290Z",
     "shell.execute_reply": "2021-05-29T15:47:57.834292Z",
     "shell.execute_reply.started": "2021-05-29T15:47:57.794286Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "      <th>Flight_1</th>\n",
       "      <th>flight_1</th>\n",
       "      <th>...</th>\n",
       "      <th>arrtime_1</th>\n",
       "      <th>arrucl_1</th>\n",
       "      <th>Flight_2</th>\n",
       "      <th>flight_2</th>\n",
       "      <th>origin_2</th>\n",
       "      <th>destination_2</th>\n",
       "      <th>deptime_2</th>\n",
       "      <th>depucl_2</th>\n",
       "      <th>arrtime_2</th>\n",
       "      <th>arrucl_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>B</td>\n",
       "      <td>512.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 19:12:00</td>\n",
       "      <td>2021-05-30 03:12:00</td>\n",
       "      <td>2021-05-29 23:29:00</td>\n",
       "      <td>2021-05-30 06:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>G</td>\n",
       "      <td>252.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 19:12:00</td>\n",
       "      <td>2021-05-30 03:12:00</td>\n",
       "      <td>2021-05-29 23:29:00</td>\n",
       "      <td>2021-05-30 06:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>H</td>\n",
       "      <td>512.0</td>\n",
       "      <td>0.33</td>\n",
       "      <td>2</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 19:12:00</td>\n",
       "      <td>2021-05-30 03:12:00</td>\n",
       "      <td>2021-05-29 23:29:00</td>\n",
       "      <td>2021-05-30 06:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>K</td>\n",
       "      <td>245.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 19:12:00</td>\n",
       "      <td>2021-05-30 03:12:00</td>\n",
       "      <td>2021-05-29 23:29:00</td>\n",
       "      <td>2021-05-30 06:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>L</td>\n",
       "      <td>341.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 17:47:00</td>\n",
       "      <td>2021-05-30 01:47:00</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 19:12:00</td>\n",
       "      <td>2021-05-30 03:12:00</td>\n",
       "      <td>2021-05-29 23:29:00</td>\n",
       "      <td>2021-05-30 06:29:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2 Class   Fare   TRD  \\\n",
       "0    BER         TFB  AA0040BERBOD  AA0104BODTFB     B  512.0  0.00   \n",
       "1    BER         TFB  AA0040BERBOD  AA0104BODTFB     G  252.0  0.00   \n",
       "2    BER         TFB  AA0040BERBOD  AA0104BODTFB     H  512.0  0.33   \n",
       "3    BER         TFB  AA0040BERBOD  AA0104BODTFB     K  245.0  0.00   \n",
       "4    BER         TFB  AA0040BERBOD  AA0104BODTFB     L  341.0  0.00   \n",
       "\n",
       "   Transfer times      Flight_1 flight_1  ...           arrtime_1  \\\n",
       "0               2  AA0040BERBOD   AA0040  ... 2021-05-29 17:47:00   \n",
       "1               2  AA0040BERBOD   AA0040  ... 2021-05-29 17:47:00   \n",
       "2               2  AA0040BERBOD   AA0040  ... 2021-05-29 17:47:00   \n",
       "3               2  AA0040BERBOD   AA0040  ... 2021-05-29 17:47:00   \n",
       "4               2  AA0040BERBOD   AA0040  ... 2021-05-29 17:47:00   \n",
       "\n",
       "             arrucl_1      Flight_2 flight_2 origin_2 destination_2  \\\n",
       "0 2021-05-30 01:47:00  AA0104BODTFB   AA0104      BOD           TFB   \n",
       "1 2021-05-30 01:47:00  AA0104BODTFB   AA0104      BOD           TFB   \n",
       "2 2021-05-30 01:47:00  AA0104BODTFB   AA0104      BOD           TFB   \n",
       "3 2021-05-30 01:47:00  AA0104BODTFB   AA0104      BOD           TFB   \n",
       "4 2021-05-30 01:47:00  AA0104BODTFB   AA0104      BOD           TFB   \n",
       "\n",
       "            deptime_2            depucl_2           arrtime_2  \\\n",
       "0 2021-05-29 19:12:00 2021-05-30 03:12:00 2021-05-29 23:29:00   \n",
       "1 2021-05-29 19:12:00 2021-05-30 03:12:00 2021-05-29 23:29:00   \n",
       "2 2021-05-29 19:12:00 2021-05-30 03:12:00 2021-05-29 23:29:00   \n",
       "3 2021-05-29 19:12:00 2021-05-30 03:12:00 2021-05-29 23:29:00   \n",
       "4 2021-05-29 19:12:00 2021-05-30 03:12:00 2021-05-29 23:29:00   \n",
       "\n",
       "             arrucl_2  \n",
       "0 2021-05-30 06:29:00  \n",
       "1 2021-05-30 06:29:00  \n",
       "2 2021-05-30 06:29:00  \n",
       "3 2021-05-30 06:29:00  \n",
       "4 2021-05-30 06:29:00  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:17:20.746243Z",
     "iopub.status.busy": "2021-05-29T16:17:20.745244Z",
     "iopub.status.idle": "2021-05-29T16:17:21.149275Z",
     "shell.execute_reply": "2021-05-29T16:17:21.147402Z",
     "shell.execute_reply.started": "2021-05-29T16:17:20.746243Z"
    }
   },
   "outputs": [],
   "source": [
    "group2['delta_time'] = (group2.arrucl_2 - group2.depucl_1).apply(lambda x:x.seconds / 60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 对于转机两次的计算分析 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:06:26.117946Z",
     "iopub.status.busy": "2021-05-29T16:06:26.116947Z",
     "iopub.status.idle": "2021-05-29T16:06:26.139958Z",
     "shell.execute_reply": "2021-05-29T16:06:26.137947Z",
     "shell.execute_reply.started": "2021-05-29T16:06:26.117946Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_trans_2():\n",
    "    group = pd.merge(groups[2],new_schedual_table,left_on='Flight1',right_on='Flight',sort=False)\n",
    "    group = pd.merge(group,new_schedual_table,left_on='Flight2',right_on='Flight',sort=False,suffixes=['_1','_2'])\n",
    "    group = pd.merge(group,new_schedual_table,left_on='Flight3',right_on='Flight',sort=False)\n",
    "    group.loc[group.arrucl_1 >= group.depucl_2,group.columns[group.dtypes == group.deptime_1.dtype][4:]] += Day(1)\n",
    "    group.loc[group.arrucl_2 >= group.depucl,group.columns[group.dtypes == group.deptime_1.dtype][8:]] += Day(1)\n",
    "    return group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:07:35.655074Z",
     "iopub.status.busy": "2021-05-29T16:07:35.654072Z",
     "iopub.status.idle": "2021-05-29T16:07:35.708079Z",
     "shell.execute_reply": "2021-05-29T16:07:35.706076Z",
     "shell.execute_reply.started": "2021-05-29T16:07:35.655074Z"
    }
   },
   "outputs": [],
   "source": [
    "group3 = process_trans_2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:07:55.914940Z",
     "iopub.status.busy": "2021-05-29T16:07:55.913940Z",
     "iopub.status.idle": "2021-05-29T16:07:55.954457Z",
     "shell.execute_reply": "2021-05-29T16:07:55.952521Z",
     "shell.execute_reply.started": "2021-05-29T16:07:55.914940Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "      <th>Flight_1</th>\n",
       "      <th>...</th>\n",
       "      <th>arrtime_2</th>\n",
       "      <th>arrucl_2</th>\n",
       "      <th>Flight</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>depucl</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>arrucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOD</td>\n",
       "      <td>EDB</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0458TFBMBY</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>B</td>\n",
       "      <td>525.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 11:40:00</td>\n",
       "      <td>2021-05-29 18:40:00</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>AA0006</td>\n",
       "      <td>MBY</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 13:05:00</td>\n",
       "      <td>2021-05-29 20:05:00</td>\n",
       "      <td>2021-05-29 20:59:00</td>\n",
       "      <td>2021-05-30 00:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOD</td>\n",
       "      <td>EDB</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0458TFBMBY</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>G</td>\n",
       "      <td>300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 11:40:00</td>\n",
       "      <td>2021-05-29 18:40:00</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>AA0006</td>\n",
       "      <td>MBY</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 13:05:00</td>\n",
       "      <td>2021-05-29 20:05:00</td>\n",
       "      <td>2021-05-29 20:59:00</td>\n",
       "      <td>2021-05-30 00:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOD</td>\n",
       "      <td>EDB</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0458TFBMBY</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>H</td>\n",
       "      <td>439.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 11:40:00</td>\n",
       "      <td>2021-05-29 18:40:00</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>AA0006</td>\n",
       "      <td>MBY</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 13:05:00</td>\n",
       "      <td>2021-05-29 20:05:00</td>\n",
       "      <td>2021-05-29 20:59:00</td>\n",
       "      <td>2021-05-30 00:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BOD</td>\n",
       "      <td>EDB</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0458TFBMBY</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>K</td>\n",
       "      <td>275.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 11:40:00</td>\n",
       "      <td>2021-05-29 18:40:00</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>AA0006</td>\n",
       "      <td>MBY</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 13:05:00</td>\n",
       "      <td>2021-05-29 20:05:00</td>\n",
       "      <td>2021-05-29 20:59:00</td>\n",
       "      <td>2021-05-30 00:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOD</td>\n",
       "      <td>EDB</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>AA0458TFBMBY</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>L</td>\n",
       "      <td>381.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>3</td>\n",
       "      <td>AA0112BODTFB</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-05-29 11:40:00</td>\n",
       "      <td>2021-05-29 18:40:00</td>\n",
       "      <td>AA0006MBYEDB</td>\n",
       "      <td>AA0006</td>\n",
       "      <td>MBY</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 13:05:00</td>\n",
       "      <td>2021-05-29 20:05:00</td>\n",
       "      <td>2021-05-29 20:59:00</td>\n",
       "      <td>2021-05-30 00:59:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2       Flight3 Class   Fare  \\\n",
       "0    BOD         EDB  AA0112BODTFB  AA0458TFBMBY  AA0006MBYEDB     B  525.0   \n",
       "1    BOD         EDB  AA0112BODTFB  AA0458TFBMBY  AA0006MBYEDB     G  300.0   \n",
       "2    BOD         EDB  AA0112BODTFB  AA0458TFBMBY  AA0006MBYEDB     H  439.0   \n",
       "3    BOD         EDB  AA0112BODTFB  AA0458TFBMBY  AA0006MBYEDB     K  275.0   \n",
       "4    BOD         EDB  AA0112BODTFB  AA0458TFBMBY  AA0006MBYEDB     L  381.0   \n",
       "\n",
       "   TRD  Transfer times      Flight_1  ...           arrtime_2  \\\n",
       "0  0.0               3  AA0112BODTFB  ... 2021-05-29 11:40:00   \n",
       "1  0.0               3  AA0112BODTFB  ... 2021-05-29 11:40:00   \n",
       "2  0.0               3  AA0112BODTFB  ... 2021-05-29 11:40:00   \n",
       "3  0.0               3  AA0112BODTFB  ... 2021-05-29 11:40:00   \n",
       "4  1.2               3  AA0112BODTFB  ... 2021-05-29 11:40:00   \n",
       "\n",
       "             arrucl_2        Flight  flight origin destination  \\\n",
       "0 2021-05-29 18:40:00  AA0006MBYEDB  AA0006    MBY         EDB   \n",
       "1 2021-05-29 18:40:00  AA0006MBYEDB  AA0006    MBY         EDB   \n",
       "2 2021-05-29 18:40:00  AA0006MBYEDB  AA0006    MBY         EDB   \n",
       "3 2021-05-29 18:40:00  AA0006MBYEDB  AA0006    MBY         EDB   \n",
       "4 2021-05-29 18:40:00  AA0006MBYEDB  AA0006    MBY         EDB   \n",
       "\n",
       "              deptime              depucl             arrtime  \\\n",
       "0 2021-05-29 13:05:00 2021-05-29 20:05:00 2021-05-29 20:59:00   \n",
       "1 2021-05-29 13:05:00 2021-05-29 20:05:00 2021-05-29 20:59:00   \n",
       "2 2021-05-29 13:05:00 2021-05-29 20:05:00 2021-05-29 20:59:00   \n",
       "3 2021-05-29 13:05:00 2021-05-29 20:05:00 2021-05-29 20:59:00   \n",
       "4 2021-05-29 13:05:00 2021-05-29 20:05:00 2021-05-29 20:59:00   \n",
       "\n",
       "               arrucl  \n",
       "0 2021-05-30 00:59:00  \n",
       "1 2021-05-30 00:59:00  \n",
       "2 2021-05-30 00:59:00  \n",
       "3 2021-05-30 00:59:00  \n",
       "4 2021-05-30 00:59:00  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T16:18:27.503015Z",
     "iopub.status.busy": "2021-05-29T16:18:27.502016Z",
     "iopub.status.idle": "2021-05-29T16:18:27.553556Z",
     "shell.execute_reply": "2021-05-29T16:18:27.551553Z",
     "shell.execute_reply.started": "2021-05-29T16:18:27.503015Z"
    }
   },
   "outputs": [],
   "source": [
    "group3['delta_time'] = (group3.arrucl - group3.depucl_1).apply(lambda x:x.seconds / 60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
